package com.xesj.util;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.codehaus.stax2.ri.typed.ValueDecoderFactory.BaseArrayDecoder;

public class MyUtils {
	
	public static List<Object>  importDataFromExcel(String fileName,File file) throws IOException{
		List<Object> list =new ArrayList<>();
		//文件流对象
		FileInputStream fis = new FileInputStream(file);
		//工作簿对象
	    Workbook workbook = null;    
		try {
			if (!file.exists()) {
				return list;
			}		
			if (fileName.endsWith(".xls")) {
				workbook = new  HSSFWorkbook(fis);			
			}else if (fileName.endsWith(".xlsx")) {
				workbook = new XSSFWorkbook(fis);
			}else {
				return list;
			}
			//获取第一个表格
			Sheet sheetAt1 = workbook.getSheetAt(0);
			int rowNums = sheetAt1.getLastRowNum();
			Row row2 = sheetAt1.getRow(2);
			if (row2==null) {
				return list;
			}
			int cellNums = row2.getLastCellNum();
			//从第二行开始读取
			for (int i = 2; i < rowNums+1; i++) {
				PageData pd = new PageData();
				Row row = sheetAt1.getRow(i);
				for (int j = 0; j < cellNums; j++) {
					Cell cell = row.getCell(j);
					cell.setCellType(Cell.CELL_TYPE_STRING);
					if (cell!=null) {
						String Value = cell.getStringCellValue();
						pd.put(j, Value);
					}	
				}
				list.add(pd);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return list;
		}finally {
			fis.close();
			workbook.close();
		}
		
		return list;
		
		
	}
	
	
	
	public static String exportDataToExcel(String title,String[] header, String [] attrs, List<Map<String,Object>> list,FileOutputStream fos){
		if (header.length!=attrs.length||header==null||attrs==null||list==null) {
			return null;	
		}
		Workbook workBook = new HSSFWorkbook();
		try {	
			//第一个表格
			Sheet sheet = workBook.createSheet(title);
			//设置表格默认列宽15个字节
	        sheet.setDefaultColumnWidth(15);
	        //生成一个样式
	        HSSFCellStyle style = getCellStyle(workBook);
	        //生成一个字体
	        HSSFFont font = getFont(workBook);
	        //把字体应用到当前样式
	        style.setFont(font);
	        //表头数据
	        Row row = sheet.createRow(0);
	        int headerLength = header.length;
	        for (int i = 0; i < headerLength; i++) {
	        	Cell cell = row.createCell(i);
	        	// 设置Excel中的背景
				style.setFillForegroundColor( HSSFColor.GREEN.index );
				style.setFillBackgroundColor( HSSFColor.GREEN.index );
				cell.setCellStyle(style);
				cell.setCellValue(new HSSFRichTextString(header[i]));
				
			}
	        //行数
	        int size = list.size();
	        if (list==null ||size==0) {
				return null;
			}
	        for (int i = 1; i <= size; i++) {
	        	//行数据,从第二行开始，i=1
	        	Row rowi = sheet.createRow(i);
	        	for (int j = 0; j < attrs.length; j++) {
	        		String key = attrs[j];
	        		String value = list.get(i-1).get(key)+"";
	        		if (StrValidate.isNull(value)) {
	        			value = "";
					}
	        		Cell cellj = rowi.createCell(j);
	        		cellj.setCellStyle(style);
	        		cellj.setCellValue(new HSSFRichTextString(value));
	        		
				}
			}
	        workBook.write(fos);
		} catch (Exception e) {
			e.printStackTrace();
			return null;	
		}finally{		
			try {
				workBook.close();
				fos.close();
			} catch (IOException e) {
				e.printStackTrace();
			}		
		}
	    return "true";		
	}
	
	
		public static String exportDataToExcelAndDownLoad(String title,String[] header, String [] attrs, List<Map<String,Object>> list,HttpServletResponse res){
			if (header.length!=attrs.length||header==null||attrs==null||list==null) {
				return null;	
			}
			Workbook workBook = new HSSFWorkbook();
			try {	
				//第一个表格
				Sheet sheet = workBook.createSheet(title);
				//设置表格默认列宽15个字节
		        sheet.setDefaultColumnWidth(15);
		        //生成一个样式
		        HSSFCellStyle style = getCellStyle(workBook);
		        //生成一个字体
		        HSSFFont font = getFont(workBook);
		        //把字体应用到当前样式
		        style.setFont(font);
		        //表头数据
		        Row row = sheet.createRow(0);
		        int headerLength = header.length;
		        for (int i = 0; i < headerLength; i++) {
		        	Cell cell = row.createCell(i);
		        	// 设置Excel中的背景
					style.setFillForegroundColor( HSSFColor.GREEN.index );
					style.setFillBackgroundColor( HSSFColor.GREEN.index );
					cell.setCellStyle(style);
					cell.setCellValue(new HSSFRichTextString(header[i]));
					
				}
		        //行数
		        int size = list.size();
		        if (list==null ||size==0) {
					return null;
				}
		        for (int i = 1; i <= size; i++) {
		        	//行数据,从第二行开始，i=1
		        	Row rowi = sheet.createRow(i);
		        	for (int j = 0; j < attrs.length; j++) {
		        		String key = attrs[j];
		        		String value = list.get(i-1).get(key)+"";
		        		if (StrValidate.isNull(value)) {
		        			value = "";
						}
		        		Cell cellj = rowi.createCell(j);
		        		cellj.setCellStyle(style);
		        		cellj.setCellValue(new HSSFRichTextString(value));
		        		
					}
				}
		        OutputStream os = res.getOutputStream();
		        res.reset();
				res.setHeader("Content-disposition", "attachment; filename=demo.xls");
				res.setContentType("application/msexcel");    
		        workBook.write(os);
		        os.close();
		        workBook.close();
			} catch (Exception e) {
				e.printStackTrace();
				return null;	
			}
		    return "true";		
		}
	//生成一个字体
	private static HSSFFont getFont(Workbook workBook) {
		  HSSFFont font = (HSSFFont) workBook.createFont();
          font.setColor(HSSFColor.WHITE.index);
          font.setFontHeightInPoints((short)12);
          font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
          return font;
	}


	//生成一个样式
	private static HSSFCellStyle getCellStyle(Workbook workBook) {
		 HSSFCellStyle style = (HSSFCellStyle) workBook.createCellStyle();
         style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
         style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
         style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
         style.setBorderTop(HSSFCellStyle.BORDER_THIN);
         style.setLeftBorderColor(HSSFCellStyle.BORDER_THIN);
         style.setRightBorderColor(HSSFCellStyle.BORDER_THIN);
         style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
         return style;
	}
	
}
